package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.ScheduleSeatDetail;

public class ScheduleSeatDetailDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(ScheduleSeatDetailDao.class);
	private static final String _SELECT_BY_ID = "select * from SCHEDULE_SEAT_DETAIL where ID = ?";
	private static final String _SELECT_ALL_BY_SCHEDULE_ID = "select * from SCHEDULE_SEAT_DETAIL where schedule_id = ? ORDER BY row desc,col asc";
	private static final String _SELECT_ALL_BY_SCHEDULE_ID_AND_SESSION_ID = "select * from SCHEDULE_SEAT_DETAIL where schedule_id = ? and session_id = ? ORDER BY row desc,col asc";
	private static final String _SELECT_ALL = "select * from SCHEDULE_SEAT_DETAIL ORDER BY row asc,col asc";
	private static final String _INSERT_NEW_SCHEDULE_SEAT_DETAIL = "INSERT INTO [SCHEDULE_SEAT_DETAIL] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER, SCHEDULE_ID, SEAT_STATUS, ROW,COL) VALUES (?,?,?,?,?,?,?,?)";
	private static final String _UPDATE_STATUS = "update SCHEDULE_SEAT_DETAIL set status = ?, session_id=?,modify_user=? where id=?";
	private static final String _DELETE_SCHEDULE_SEAT_DETAIL = "DELETE FROM SCHEDULE_SEAT_DETAIL WHERE ID = ?";

	private static final String _UPDATE_SEAT_STATUS = "UPDATE SCHEDULE_SEAT_DETAIL SET SEAT_STATUS = ?, SESSION_ID=?, MODIFY_USER=?, MODIFY_DATE=getdate() WHERE ID = ?";

	private static ScheduleSeatDetailDao scheduleSeatDetailDao = null;

	public static ScheduleSeatDetailDao getInstance() {
		if (scheduleSeatDetailDao == null) {
			scheduleSeatDetailDao = new ScheduleSeatDetailDao();
		}
		return scheduleSeatDetailDao;
	}

	public ScheduleSeatDetail retrieveScheduleSeatDetail(long id) {
		ScheduleSeatDetail scheduleSeatDetail = null;
		Connection connection = null;
		PreparedStatement pstat = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			pstat = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstat.setLong(1, id);
			result = pstat.executeQuery();
			if (result.next()) {
				scheduleSeatDetail = new ScheduleSeatDetail();
				scheduleSeatDetail.setId(result.getLong("id"));
				scheduleSeatDetail.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				scheduleSeatDetail.setCreateUser(result.getLong("create_user"));
				scheduleSeatDetail.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				scheduleSeatDetail.setModifyUser(result.getLong("modify_user"));
				scheduleSeatDetail.setScheduleId(result.getLong("SCHEDULE_ID"));
				scheduleSeatDetail.setSeatStatus(result.getString("SEAT_STATUS"));
				scheduleSeatDetail.setRow(result.getString("ROW"));
				scheduleSeatDetail.setCol(result.getInt("COL"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveScheduleSeatDetail ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(pstat);
			DbUtils.closeQuietly(connection);
		}
		return scheduleSeatDetail;
	}
	
	public List<ScheduleSeatDetail> retrieveSeatListByScheduleAndSessionId(long scheduleId, String sessionId){
		List<ScheduleSeatDetail> seatDetailList = new ArrayList<ScheduleSeatDetail>();
		ScheduleSeatDetail scheduleSeatDetail = null;
		Connection connection = null;
		PreparedStatement pstat = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			pstat = connection.prepareStatement(_SELECT_ALL_BY_SCHEDULE_ID_AND_SESSION_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstat.setLong(1, scheduleId);
			pstat.setString(2, sessionId);
			result = pstat.executeQuery();
			while (result.next()) {
				scheduleSeatDetail = new ScheduleSeatDetail();
				scheduleSeatDetail.setId(result.getLong("id"));
				scheduleSeatDetail.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				scheduleSeatDetail.setCreateUser(result.getLong("create_user"));
				scheduleSeatDetail.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				scheduleSeatDetail.setModifyUser(result.getLong("modify_user"));
				scheduleSeatDetail.setScheduleId(result.getLong("SCHEDULE_ID"));
				scheduleSeatDetail.setSeatStatus(result.getString("SEAT_STATUS"));
				scheduleSeatDetail.setRow(result.getString("ROW"));
				scheduleSeatDetail.setCol(result.getInt("COL"));
				seatDetailList.add(scheduleSeatDetail);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveScheduleSeatDetail ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(pstat);
			DbUtils.closeQuietly(connection);
		}
		return seatDetailList;
	}
	
	public List<ScheduleSeatDetail> retrieveSeatListBySchedule(long scheduleId) {
		List<ScheduleSeatDetail> seatDetailList = new ArrayList<ScheduleSeatDetail>();
		ScheduleSeatDetail scheduleSeatDetail = null;
		Connection connection = null;
		PreparedStatement pstat = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			pstat = connection.prepareStatement(_SELECT_ALL_BY_SCHEDULE_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstat.setLong(1, scheduleId);
			result = pstat.executeQuery();
			while (result.next()) {
				scheduleSeatDetail = new ScheduleSeatDetail();
				scheduleSeatDetail.setId(result.getLong("id"));
				scheduleSeatDetail.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				scheduleSeatDetail.setCreateUser(result.getLong("create_user"));
				scheduleSeatDetail.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				scheduleSeatDetail.setModifyUser(result.getLong("modify_user"));
				scheduleSeatDetail.setScheduleId(result.getLong("SCHEDULE_ID"));
				scheduleSeatDetail.setSeatStatus(result.getString("SEAT_STATUS"));
				scheduleSeatDetail.setRow(result.getString("ROW"));
				scheduleSeatDetail.setCol(result.getInt("COL"));
				seatDetailList.add(scheduleSeatDetail);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveScheduleSeatDetail ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(pstat);
			DbUtils.closeQuietly(connection);
		}
		return seatDetailList;
	}

	public List<ScheduleSeatDetail> retrieveAllScheduleSeatDetail() {
		List<ScheduleSeatDetail> scheduleSeatDetailList = new ArrayList<ScheduleSeatDetail>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			ScheduleSeatDetail scheduleSeatDetail;
			while (result.next()) {
				scheduleSeatDetail = this.retrieveScheduleSeatDetail(result.getInt("id"));
				scheduleSeatDetailList.add(scheduleSeatDetail);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveScheduleSeatDetail ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}

		return scheduleSeatDetailList;
	}

	public void addScheduleSeatDetail(ScheduleSeatDetail scheduleSeatDetail, long userId) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			connection = getConnection();
			pstmt = connection.prepareStatement(_INSERT_NEW_SCHEDULE_SEAT_DETAIL,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			pstmt.setTimestamp(1, toSQLDate(new Date()));
			pstmt.setLong(2, userId);
			pstmt.setTimestamp(3, toSQLDate(new Date()));
			pstmt.setLong(4, userId);
			pstmt.setLong(5, scheduleSeatDetail.getScheduleId());
			pstmt.setString(6, scheduleSeatDetail.getSeatStatus());
			pstmt.setString(7, scheduleSeatDetail.getRow());
			pstmt.setInt(8, scheduleSeatDetail.getCol());

			int rows = pstmt.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to create seat ", e);
		} finally {
			DbUtils.closeQuietly(pstmt);
			DbUtils.closeQuietly(connection);
		}
	}

	public void deleteScheduleSeatDetail(long id) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			connection = getConnection();
			pstmt = connection.prepareStatement(_DELETE_SCHEDULE_SEAT_DETAIL,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			pstmt.setLong(1, id);

			// for this deletion, we assumed the provided id is exist.
			int rows = pstmt.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to create schedule Seat Detail ", e);
		} finally {
			DbUtils.closeQuietly(pstmt);
			DbUtils.closeQuietly(connection);
		}
	}

	public void updateSeatStatus(long seatId, String newStatus,String sessionId,long modifyUser){

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet result = null;
		try {
			connection = getConnection();
			pstmt = connection.prepareStatement(_UPDATE_SEAT_STATUS);
			pstmt.setString(1, newStatus);
			pstmt.setString(2, sessionId);
			pstmt.setLong(3, modifyUser);
			pstmt.setLong(4, seatId);
			int rows = pstmt.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to update seat status ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(pstmt);
			DbUtils.closeQuietly(connection);
		}
	}
}

